﻿Imports System.Data
Imports System.Data.SqlClient
Imports Entidad

Public Class DocumentoAlmacenPickeadorDatos

    Function obtenerCodigoGeneradoDPA(tipoDOC As String) As Integer
        Dim numero As Integer = 0
        Dim ds As New DataSet
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection
        Try
            coneccion = conex.conectar()
            coneccion.Open()
            Dim query As String = "SELECT COUNT(*) AS Contar " &
                                  "FROM Documento_Almacen_Pickeador " &
                                  "WHERE TDOC_Codigo = '" & tipoDOC & "'"

            Dim adapter As SqlDataAdapter = New SqlDataAdapter(query, coneccion)
            adapter.Fill(ds)
        Catch ex As SqlException
            MsgBox("La conexión no está disponible.")
        Catch e As Exception
            MsgBox(e.Message)
        Finally
            coneccion.Close()
        End Try

        numero = CInt(ds.Tables(0).Rows(0)("Contar").ToString.Trim) + 1

        Return numero
    End Function

    Sub registrarDocumentoAlmacenPickeador(ByVal dap As DocumentoAlmacenPickeador)
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "INSERT INTO Documento_Almacen_Pickeador(CIA,SUC,TDOC_Codigo,DCA_Codigo, " & _
                              "DAP_Codigo,ALM_Codigo,PICK_Codigo,DAP_FechaAsig,DAP_FechaFinAsig,DAP_Orden,DAP_Activo) " & _
                              "VALUES(@cia,@sucursal,@tipo,@documento,@codigo,@almacen,@pickeador, " & _
                              "getDATE(),null,@orden,@activo)"

        Dim comando As New SqlCommand
        With comando
            .Connection = coneccion
            .CommandText = query
            .Parameters.AddWithValue("@cia", dap.CIA)
            .Parameters.AddWithValue("@sucursal", dap.SUC)
            .Parameters.AddWithValue("@tipo", dap.TIPODOC)
            .Parameters.AddWithValue("@documento", dap.DCACOD)
            .Parameters.AddWithValue("@codigo", dap.DAPCOD)
            .Parameters.AddWithValue("@almacen", dap.ALM)
            .Parameters.AddWithValue("@pickeador", dap.PICKCOD)
            .Parameters.AddWithValue("@orden", dap.ORDEN)
            .Parameters.AddWithValue("@activo", dap.ACTIVO)
            .ExecuteNonQuery()
        End With
        coneccion.Close()
    End Sub

    Function obtenerCodigo(ByVal codPedido As String, ByVal codPick As String, tipoDOC As String) As Integer
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "SELECT TOP 1 DAP_Codigo " &
                              "FROM Documento_Almacen_Pickeador " &
                              "WHERE DCA_Codigo = '" & codPedido & "' " &
                              "AND PICK_Codigo = '" & codPick & "' " &
                              "AND TDOC_Codigo = '" & tipoDOC & "' " &
                              "ORDER BY DAP_Codigo DESC"

        Dim adapter As New SqlDataAdapter(query, coneccion)
        Dim ds As New DataSet
        adapter.Fill(ds)
        coneccion.Close()

        Return CInt(ds.Tables(0).Rows(0)("DAP_Codigo").ToString.Trim)
    End Function

    Function verificarExistenciaDocumentoAlmacenPickeador(ByVal codPedido As String, ByVal codPick As String, tipoDOC As String) As Boolean
        Dim valor As Boolean = False
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "SELECT DCA_Codigo, PICK_Codigo " &
                              "FROM Documento_Almacen_Pickeador " &
                              "WHERE DCA_Codigo = '" & codPedido & "' " &
                              "AND PICK_Codigo = '" & codPick & "' " &
                              "AND DAP_Activo = '1' " &
                              "AND TDOC_Codigo = '" & tipoDOC & "' " &
                              "AND DAP_FechaFinAsig IS NULL"

        Dim adapter As New SqlDataAdapter(query, coneccion)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        If ds.Tables(0).Rows.Count > 0 Then
            valor = True
        End If

        Return valor
    End Function

    Function ultimoNumero(ByVal pickCod As String) As Integer
        Dim num As Integer = 0

        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "SELECT TOP 1 DAP_Orden " &
                              "FROM Documento_Almacen_Pickeador " &
                              "WHERE PICK_Codigo = '" & pickCod & "' " &
                              "AND DAP_Activo = '1' " &
                              "AND TDOC_Codigo = 'P' " &
                              "ORDER BY DAP_Orden DESC"

        Dim adapter As New SqlDataAdapter(query, coneccion)
        Dim ds As New DataSet
        adapter.Fill(ds)
        coneccion.Close()

        If ds.Tables(0).Rows.Count <> 0 Then
            num = CInt(ds.Tables(0).Rows(0)("DAP_Orden").ToString.Trim)
        End If

        Return num

    End Function

    Sub priorizarPedidoXPickeador(ByVal codPedido As String, ByVal codPick As String)
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim comando As New SqlCommand
        With comando
            .Connection = coneccion
            .CommandText = "usp_ordenarPedidos"
            .CommandType = CommandType.StoredProcedure
            .Parameters.AddWithValue("@pickCodigo", codPick)
            .Parameters.AddWithValue("@pedCodigo", codPedido)
            .ExecuteNonQuery()
        End With
        coneccion.Close()
    End Sub

    Sub actualizarFechaFinAsignacion(ByVal codPedido As String, ByVal codPick As String, tipoDOC As String)
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "UPDATE Documento_Almacen_Pickeador " &
                              "SET DAP_FechaFinAsig = GETDATE(), DAP_Activo = '0' " &
                              "WHERE CIA = @CIA AND SUC = @SUC AND TDOC_Codigo = @TDO " &
                              "AND DCA_Codigo = @DCA AND PICK_Codigo = @PICK AND DAP_Activo = '1'"

        Dim comando As New SqlCommand
        With comando
            .Connection = coneccion
            .CommandText = query
            .Parameters.AddWithValue("@CIA", "01")
            .Parameters.AddWithValue("@SUC", "01")
            .Parameters.AddWithValue("@TDO", tipoDOC)
            .Parameters.AddWithValue("@DCA", codPedido)
            .Parameters.AddWithValue("@PICK", codPick)
            .ExecuteNonQuery()
        End With
        coneccion.Close()
    End Sub

    Function obtenerFechasAsignacion(codPedido As String) As DataSet
        Dim ds As New DataSet
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection
        Try
            coneccion = conex.conectar()
            coneccion.Open()

            Dim query As String = "SELECT dc.CIA, dc.SUC_Codigo, dc.TDOC_Codigo, dc.DCI_Codigo, CONVERT(VARCHAR(15),dc.DCI_Fecha,103) + ' ' + " &
                                  "CONVERT(VARCHAR(15),dc.DCI_Fecha,108) AS Fecha, " &
                                  "CAST(SUM(ve1.TAsignador) / 60 AS VARCHAR(15)) + ' h. ' + CAST(SUM(ve1.TAsignador) % 60 AS VARCHAR(15)) + ' min.' AS TAsignador " &
                                  "FROM Documento_Cliente dc " &
                                  "INNER JOIN Documento_Cliente_Detalle dcd " &
                                  "ON dcd.CIA = dc.CIA AND dcd.SUC = dc.SUC_Codigo AND dcd.TDOC_Codigo = dc.TDOC_Codigo " &
                                  "AND dcd.DCI_Codigo = dc.DCI_Codigo AND dcd.DCD_Activo = '1' " &
                                  "INNER JOIN Documento_Cliente_Almacen_Detalle dcad " &
                                  "ON dcad.CIA_Cliente = dcd.CIA AND dcad.SUC_Cliente = dcd.SUC AND dcad.TDOC_Cliente = dcd.TDOC_Codigo " &
                                  "AND dcad.DCI_Codigo = dcd.DCI_Codigo AND dcad.DCD_Codigo = dcd.DCD_Codigo AND dcad.DCAD_Activo = '1' " &
                                  "INNER JOIN VW_VistaDocumentoAlmacen da " &
                                  "ON da.CIA = dcad.CIA_Almacen AND da.SUC_Codigo = dcad.SUC_Almacen AND da.TDOC_Codigo = dcad.TDOC_Almacen " &
                                  "AND da.DCA_Codigo = dcad.DCA_Codigo AND da.DAD_Codigo = dcad.DAD_Codigo " &
                                  "LEFT JOIN VW_VistaEventos ve1 " &
                                  "ON ve1.CIA = da.CIA AND ve1.SUC_Codigo = da.SUC_Codigo AND ve1.TDOC_Codigo = da.TDOC_Codigo " &
                                  "AND ve1.DCA_Codigo = da.DCA_Codigo AND ve1.DAP_Codigo = da.DAP_Codigo AND ve1.EVE_Codigo = '07' " &
                                  "WHERE dc.DCI_Codigo = @DCI " &
                                  "GROUP BY dc.CIA, dc.SUC_Codigo, dc.TDOC_Codigo, dc.DCI_Codigo, dc.DCI_Fecha"

            Dim comando As New SqlCommand(query, coneccion)
            With comando
                .Parameters.AddWithValue("@DCI", codPedido)
            End With
            Dim adapter As New SqlDataAdapter(comando)
            adapter.Fill(ds)
        Catch ex As SqlException
            MsgBox("La conexión no está disponible.")
        Catch e As Exception
            MsgBox(e.Message)
        Finally
            coneccion.Close()
        End Try

        Return ds
    End Function

    Function obtenerFechaTotalPicking(codPedido As String) As DataSet
        Dim ds As New DataSet
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection
        Try
            coneccion = conex.conectar()
            coneccion.Open()

            Dim query As String = "SELECT vdc.CIA_Cliente, vdc.SUC_Cliente, vdc.TDOC_Cliente, vdc.DCI_Codigo, SUM(ve1.TAsignacion) AS TAsignacion, " &
                                  "CAST(SUM(ISNULL(ve2.TPicking, 0)) / 60 AS VARCHAR(15)) + ' h. ' + CAST(SUM(ISNULL(ve2.TPicking, 0)) % 60 AS VARCHAR(15)) + ' min.' AS TPicking, " &
                                  "SUM(ISNULL(ve3.TStandBy,0)) AS TStandBy " &
                                  "FROM VW_VistaDocumentoClientePickeador vdc " &
                                  "LEFT JOIN VW_VistaEventos ve1 " &
                                  "ON ve1.CIA = vdc.CIA_Almacen AND ve1.SUC_Codigo = vdc.SUC_Almacen AND ve1.TDOC_Codigo = vdc.TDOC_Almacen " &
                                  "AND ve1.DCA_Codigo = vdc.DCA_Codigo AND ve1.DAP_Codigo = vdc.DAP_Codigo AND ve1.EVE_Codigo = '01' " &
                                  "LEFT JOIN VW_VistaEventos ve2 " &
                                  "ON ve2.CIA = vdc.CIA_Almacen AND ve2.SUC_Codigo = vdc.SUC_Almacen AND ve2.TDOC_Codigo = vdc.TDOC_Almacen " &
                                  "AND ve2.DCA_Codigo = vdc.DCA_Codigo AND ve2.DAP_Codigo = vdc.DAP_Codigo AND ve2.EVE_Codigo = '05' " &
                                  "LEFT JOIN VW_VistaEventos ve3 " &
                                  "ON ve3.CIA = vdc.CIA_Almacen AND ve3.SUC_Codigo = vdc.SUC_Almacen AND ve3.TDOC_Codigo = vdc.TDOC_Almacen " &
                                  "AND ve3.DCA_Codigo = vdc.DCA_Codigo AND ve3.DAP_Codigo = vdc.DAP_Codigo AND ve3.EVE_Codigo = '03' " &
                                  "WHERE vdc.DCI_Codigo = @DCI " &
                                  "GROUP BY vdc.CIA_Cliente, vdc.SUC_Cliente, vdc.TDOC_Cliente, vdc.DCI_Codigo"

            Dim comando As New SqlCommand(query, coneccion)
            With comando
                .Parameters.AddWithValue("@DCI", codPedido)
            End With
            Dim adapter As New SqlDataAdapter(comando)
            adapter.Fill(ds)
        Catch ex As SqlException
            MsgBox("La conexión no está disponible.")
        Catch e As Exception
            MsgBox(e.Message)
        Finally
            coneccion.Close()
        End Try

        Return ds
    End Function

    Function obtenerFechasAsignador(codPedido As String) As DataSet
        Dim ds As New DataSet
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection
        Try
            coneccion = conex.conectar()
            coneccion.Open()

            Dim query As String = "SELECT CONVERT(VARCHAR(15),dapt.DAPT_FechaIni,103) + ' ' + CONVERT(VARCHAR(15),dapt.DAPT_FechaIni,108) AS Fecha1, " &
                                  "CONVERT(VARCHAR(15),dapt.DAPT_FechaFin,103) + ' ' + CONVERT(VARCHAR(15),dapt.DAPT_FechaFin,108) AS Fecha2 " &
                                  "FROM VW_VistaDocumentoClientePickeador vdc " &
                                  "INNER JOIN Documento_Almacen_Pickeador_Tiempo dapt " &
                                  "ON dapt.CIA = vdc.CIA_Almacen AND dapt.SUC_Codigo = vdc.SUC_Almacen AND dapt.TDOC_Codigo = vdc.TDOC_Almacen " &
                                  "AND dapt.DCA_Codigo = vdc.DCA_Codigo AND dapt.DAP_Codigo = vdc.DAP_Codigo AND dapt.DAPT_Activo = '1' " &
                                  "AND dapt.EVE_Codigo = '07' " &
                                  "WHERE vdc.CIA_Cliente = '01' AND vdc.SUC_Cliente = '01' AND vdc.TDOC_Cliente = 'P' AND vdc.DCI_Codigo = @DCI " &
                                  "ORDER BY dapt.DAPT_Codigo"

            Dim comando As New SqlCommand(query, coneccion)
            With comando
                .Parameters.AddWithValue("@DCI", codPedido)
            End With
            Dim adapter As New SqlDataAdapter(comando)
            adapter.Fill(ds)
        Catch ex As SqlException
            MsgBox("La conexión no está disponible.")
        Catch e As Exception
            MsgBox(e.Message)
        Finally
            coneccion.Close()
        End Try

        Return ds
    End Function

End Class
